<?php

  class MsSQL extends Base {

    /** @var resource The MS SQL link identifier. */
    private $handle;

    /** @var resource Statement resource obtained by MsSQL::Init(). */
    private $resource;

    /** @var boolean Property used to define is class is connected or not */
    private $connected = false;

    /** @var IString The host name of the MsSQL server. */
    private $host = "";

    /** @var IString The login to connect to the database. */
    private $login = "";

    /** @var IString The password to connect to the database. */
    private $password = "";

    /** @var IString The database to work with. */
    private $database = "";

    /** @var Logger The logger. */
    private static $LOG = null;

    /**
     * Store all parameters in private variables.
     *
     * @param string $host The MS SQL server. It can also include a port number. e.g. hostname,port.
     * @param string $login The username.
     * @param string $password The password.
     * @param string $database The database name. If the database name does'nt starts with a bracket, it will be added at the beginning and the end.
     */
    public function __construct($host = "", $login = "", $password = "", $database = "") {
      self::$LOG = Logger::getLogger("beobject.sql.mssql");
      if ($login == "sa") {
        self::$LOG->warn("CONNECTED as administrator", __CLASS__ . "::" . __METHOD__);
      }

      $this->host = new String($host);
      $this->login = new String($login);
      $this->password = new String($password);
      $this->database = new String($database);

      if (Server::getPhpVersion()->subString(new Integer(0), new Integer(3))->replace(new String("."), new String(String::EMPTY_STRING))->toString() < "53") {
        self::$LOG->info("Loading SQL driver for PHP < 5.3", __CLASS__ . "::" . __METHOD__);
        $this->handle = mssql_connect($this->host->toString(), $this->login->toString(), $this->password->toString());

        if ($this->database->toString() != String::EMPTY_STRING) {
          if ($database[0] != "[") {
            mssql_select_db("[" . $this->database->toString() . "]");
          } else {
            mssql_select_db($this->database->toString());
          }
        }
      } else {
        self::$LOG->info("Loading SQL driver for PHP >= 5.3", __CLASS__ . "::" . __METHOD__);
        $this->handle = sqlsrv_connect($this->host->toString(), array(
            "Database" => $this->database->trim(new String("[]"))->toString(),
            "UID" => $this->login->toString(),
            "PWD" => $this->password->toString()));
      }

      if (is_resource($this->handle)) {
        self::$LOG->debug("Connected " . $login . "/" . $password . " to " . $host . "." . $database, __CLASS__ . "::" . __METHOD__);
        $this->connected = true;
      } else {
        self::$LOG->error("Can not connect to " . $host . " with " . $login . "/" . $password, __CLASS__ . "::" . __METHOD__);
      }
    }

    /**
     * Property used to define if class is connected or not.<br />
     * @return boolean TRUE if connected, FALSE otherwise.
     */
    public function isConnected() {
      return $this->connected;
    }

    /**
     * Returns the host name of the current MsSQL server.<br />
     * @return string The host name.
     */
    public function getHost() {
      return $this->host;
    }

    /**
     * Returns the login to connect the database.<br />
     * @return string The login.
     */
    public function getLogin() {
      return $this->login;
    }

    /**
     * Returns the password to connect to the database.<br />
     * @return string The password.
     */
    public function getPassword() {
      return $this->password;
    }

    /**
     * Returns the current database name.<br />
     * @return string The database name.
     */
    public function getDataBase() {
      return $this->database;
    }

    /**
     * Set the host name for the MsSql server.<br />
     * @param string $value The host name to connect to.
     */
    public function setHost($value) {
      if (Validator::Validate($value, Validator::STRING))
        $this->host = $value;
    }

    /**
     * Set the login to connect to the database.<br />
     * @param string $value The login for connecting to the database.
     */
    public function setLogin($value) {
      if (Validator::Validate($value, Validator::STRING))
        $this->login = $value;
    }

    /**
     * Set the password to connect to the database.<br />
     * @param string $value The password to connect to the database.
     */
    public function setPassword($value) {
      if (Validator::Validate($value, Validator::STRING))
        $this->password = $value;
    }

    /**
     * Set the database to work with.<br />
     * @param string $value The database name to work with. If the database name
     * does'nt starts with a bracket, it will be added at the beginning and the
     * end.
     */
    public function setDataBase($value) {
      if (Validator::Validate($value, Validator::STRING))
        if ($value[0] != "[")
          $this->database = "[" . $value . "]";
        else
          $this->database = $value;
    }

    /**
     * Adds a parameter to a stored procedure or a remote stored procedure.<br />
     *
     * @param string $parameter The parameter name, as a string.
     *
     * @param mixed $var The PHP variable you'll bind the MSSQL parameter to. You can
     * pass it by value, or by reference, to retrieve OUTPUT and RETVAL values
     * after the procedure execution.
     *
     * @param integer $type One of: SQLTEXT, SQLVARCHAR, SQLCHAR, SQLINT1, SQLINT2,
     * SQLINT4, SQLBIT, SQLFLT4, SQLFLT8, SQLFLTN.
     *
     * @param boolean $is_output Whether the value is an OUTPUT parameter or not. If
     * it's an OUTPUT parameter and you don't mention it, it will be treated as
     * a normal input parameter and no error will be thrown.
     *
     * @param boolean $is_null Whether the parameter is NULL or not. Passing the NULL
     * value as var  will not do the job.
     *
     * @param integer $max_length Used with char/varchar values. You have to indicate
     * the length of the data so if the parameter is a varchar(50), the type
     * must be SQLVARCHAR and this value 50.
     *
     * @return boolean TRUE on success or FALSE on failure.<br /><br />
     *
     * @see <a href="http://fr2.php.net/manual/en/function.mssql-bind.php">mssql_bind()</a>
     */
    public function bind($parameter, &$var, $type = SQLVARCHAR, $is_output = FALSE, $is_null = FALSE, $max_length = -1) {
      if ($max_length != -1)
        return mssql_bind($this->resource, $parameter, $var, $type, $is_output, $is_null, $max_length);
      else
        return mssql_bind($this->resource, $parameter, $var, $type, $is_output, $is_null);
    }

    /**
     * Close MS SQL Server connection.<br />
     * Closes the link to a MS SQL Server database.
     *
     * @return boolean TRUE on success or FALSE on failure.<br /><br />
     * @see <a href="http://be2.php.net/manual/en/function.mssql-close.php">mssql_close()</a>
     */
    public function close() {
      return mssql_close($this->handle);
    }

    /**
     * Open MS SQL connection.<br />
     * If persistent is TRUE, at first, when connecting, the function would
     * first try to find a (persistent) link that's already open with the same
     * host, username and password. If one is found, an identifier for it will
     * be returned instead of opening a new connection. Second, the connection
     * to the SQL server will not be closed when the execution of the script
     * ends. Instead, the link will remain open for future use (Close() will not
     * close persistent links established).<br />
     *
     * @param boolean $persist If set to TRUE, the connection link will be persistent.
     *
     * @return TRUE on success, FALSE otherwise.<br /><br />
     *
     * @see <a href="http://www.php.net/manual/en/function.mssql-connect.php">mssql_connect</a>
     * @see <a href="http://www.php.net/manual/en/function.mssql-pconnect.php">mssql_pconnect()</a>
     */
    public function connect($persist = FALSE) {
      if ($persist)
        $this->handle = mssql_pconnect($this->host, $this->login, $this->password);
      else
        $this->handle = mssql_connect($this->host, $this->login, $this->password);

      return!empty($this->handle);
    }

    /**
     * Moves internal row pointer.<br />
     * Moves the internal row pointer of the MS SQL result associated with the
     * specified result identifier to point to the specified row number, first
     * row being number 0. The next call to FetchRow()  would return that
     * row.<br />
     *
     * @param integer $row_number The desired row number of the new result pointer.
     *
     * @return boolean TRUE on success or FALSE on failure.<br /><br />
     *
     * @see <a href="http://www.php.net/manual/en/function.mssql-data-seek.php">mssql_data_seek()</a>
     */
    public function dataSeek($row_number) {
      if (Validator::Validate($row_number, Validator::INTEGER))
        return mssql_data_seek($this->handle, $row_number);
    }

    /**
     * Executes a stored procedure on a MS SQL server database.<br />
     * @param boolean $skip_results Whenever to skip the results or not.
     * @see <a href="http://www.php.net/manual/en/function.mssql-execute.php">mssql_execute()</a>
     */
    public function execute($skip_results = FALSE) {
      return mssql_execute($this->resource, $skip_results);
    }

    /**
     * Send MS SQL query.<br />
     * Sends a query to the currently active database on the server.<br />
     * Note that unescaped quotes are escaped with a regular expression.<br />
     *
     * @param string $query A SQL query.
     *
     * @param integer $batch_size The number of records to batch in the buffer.
     *
     * @return resource A MS SQL result resource on success, TRUE if no rows were
     * returned, or FALSE on error.<br /><br />
     *
     * @see <a href="http://www.php.net/manual/en/function.mssql-query.php">mssql_query()</a>
     * @see <a href="http://be.php.net/manual/en/function.preg-replace.php">preg_replace()</a>
     */
    public function executeQuery($query, $batch_size = 0) {
      self::$LOG->debug($query, __CLASS__ . "::" . __METHOD__);
      if (Server::getPhpVersion()->subString(new Integer(0), new Integer(3))->toString() != "5.3") {
        if ($batch_size > 0) {
          return mssql_query(preg_replace("/(\w)'{1}(\w)/", "$1''$2", $query), $this->handle, $batch_size);
        } else {
          return mssql_query(preg_replace("/(\w)'{1}(\w)/", "$1''$2", $query), $this->handle);
        }
      } else {
        return sqlsrv_query($this->handle, preg_replace("/(\w)'{1}(\w)/", "$1''$2", $query));
      }
    }

    /**
     * Fetch a result row as an associative array, a numeric array, or both.<br />
     * This method is an extended version of FetchRow(). In addition to storing
     * the data in the numeric indices of the result array, it also stores the
     * data in associative indices, using the field names as keys.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This result
     * comes from a call to ExecuteQuery().
     *
     * @param constant $type The type of array that is to be fetched. It's a constant and
     * can take the following values: MSSQL_ASSOC, MSSQL_NUM, and the default
     * value of MSSQL_BOTH.
     *
     * @return array An array that corresponds to the fetched row, or FALSE if there
     * are no more rows.<br /><br />
     *
     * @see <a href="http://www.php.net/manual/en/function.mssql-fetch-array.php">mssql_fetch_array()</a>
     */
    public function fetchArray($result, $type = 1) {
      if (Server::getPhpVersion()->subString(new Integer(0), new Integer(3))->toString() != "5.3") {
        return mssql_fetch_array($result, $type);
      } else {
        return sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
      }
    }

    /**
     * Returns an associative array of the current row in the result.<br />
     * Returns an associative array that corresponds to the fetched row and
     * moves the internal data pointer ahead. FetchAssoc() is equivalent to
     * calling FetchArray() with MSSQL_ASSOC for the second parameter.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     * @return array Returns an associative array of the current row in
     * the result.<br /><br />
     * @see <a href="http://www.php.net/manual/en/function.mssql-fetch-assoc.php">mssql_fetch_assoc()</a>
     */
    public function fetchAssoc($Result) {
      return mssql_fetch_assoc($Result);
    }

    /**
     * Returns the next batch of records.<br />
     *
     * @param resource The result resource that is being evaluated. This result
     * comes from a call to ExecuteQuery().
     *
     * @return integer The batch number.<br /><br />
     *
     * @see <a href="http://www.php.net/manual/en/function.mssql-fetch-batch.php">mssql_fetch_batch()</a>
     */
    public function fetchBatch($Result) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        return mssql_fetch_batch($Result);
    }

    /**
     * Get field information.<br />
     * This method can be used in order to obtain information about fields in a
     * certain query result.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @param integer $offset The numerical field offset. If the field
     * offset is not specified, the next field that was not yet retrieved by
     * this function is retrieved. The field_offset  starts at 0.
     *
     * @return object An object containing field information. The properties of
     * the object are name, column_source, max_length, numeric and type.<br /><br />
     *
     * @see <a href="http://www.php.net/manual/en/function.mssql-fetch-field.php">mssql_fetch_field()</a>
     */
    public function fetchField($Result, $offset = NULL) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        if (!empty($offset))
          if (Validator::Validate($offset, Validator::INTEGER))
            return mssql_fetch_field($Result, $offset);
          else
            return mssql_fetch_field($Result);
    }

    /**
     * Fetch row as object.<br />
     * This function is similar to FetchArray(), with one difference - an object
     * is returned, instead of an array. Indirectly, that means that you can
     * only access the data by the field names, and not by their offsets
     * (numbers are illegal property names). Speed-wise, the function is
     * identical to FetchArray(), and almost as quick as FetchRow() (the
     * difference is insignificant).<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @return object An object with properties that correspond to the fetched
     * row, or FALSE if there are no more rows.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-fetch-object.php">mssql_fetch_object()</a>
     */
    public function fetchObject($Result) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        return mssql_fetch_object($Result);
    }

    /**
     * Get row as enumerated array.<br />
     * This function fetches one row of data from the result associated with the
     * specified result identifier. The row is returned as an array. Each result
     * column is stored in an array offset, starting at offset 0. Subsequent
     * call to FetchRow() would return the next row in the result set, or FALSE
     * if there are no more rows.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @return array An array that corresponds to the fetched row, or FALSE if
     * there are no more rows.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-fetch-row.php">mssql_fetch_row()</a>
     */
    public function fetchRow($Result) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        return mssql_fetch_row($Result);
    }

    /**
     * Seeks to the specified field offset.<br />
     * Seeks to the specified field offset. If the next call to FetchField()
     * won't include a field offset, this field would be returned.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @param integer $offset The field offset, starts at 0.
     *
     * @return boolean TRUE on success or FALSE on failure.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-field-seek.php">mssql_field_seek()</a>
     */
    public function fieldSeek($Result, $offset = 0) {
      if (Validator::Validate($Result, Validator::RESOURCE) &&
              Validator::Validate($offset, Validator::INTEGER))
        return mssql_field_seek($Result, $offset);
    }

    /**
     * Returns the number of records affected by the query.<br />
     *
     * @return integer The number of records affected by the query.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-rows-affected.php">mssql_rows_affected()</a>
     */
    public function getAffectedRows() {
      return mssql_rows_affected($this->handle);
    }

    /**
     * Get the length of a field.<br />
     * Returns the length of field no. offset  in result.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @param integer $offset The field offset, starts at 0. If omitted, the
     * current field is used.
     *
     * @return integer The length of the specified field index on success, or
     * FALSE on failure.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-field-length.php">mssql_field_length()</a>
     */
    public function getFieldLength($Result, $offset = 0) {
      if (Validator::Validate($Result, Validator::RESOURCE) &&
              Validator::Validate($offset, Validator::INTEGER))
        return mssql_field_length($Result, $offset);
    }

    /**
     * Get the name of a field.<br />
     * Returns the name of field no. $offset in result.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @param integer $offset The field offset, starts at 0. If
     * omitted, the current field is used.
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-field-name.php">mssql_field_name()</a>
     */
    public function getFieldName($Result, $offset = 0) {
      if (Validator::Validate($Result, Validator::RESOURCE) &&
              Validator::Validate($offset, Validator::INTEGER))
        return mssql_field_name($Result, $offset);
    }

    /**
     * Gets the number of fields in result.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @return integer The number of fields.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-num-fields.php">mssql_num_fields()</a>
     */
    public function getFieldsNumber($Result) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        return mssql_num_fields($Result);
    }

    /**
     * Gets the type of a field.<br />
     * Returns the type of field no. $offset in result.<br />
     *
     * @param resource $Result The result resource that is being evaluated. This
     * result comes from a call to ExecuteQuery().
     *
     * @param integer $offset The field offset, starts at 0. If omitted, the
     * current field is used.
     *
     * @return mixed The type of the specified field index on success, or FALSE
     * on failure.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-field-type.php">mssql_field_type()</a>
     */
    public function getFieldType($Result, $offset = 0) {
      if (Validator::Validate($Result, Validator::RESOURCE) &&
              Validator::Validate($offset, Validator::INTEGER))
        return mssql_field_type($Result, $offset);
    }

    /**
     * Returns the last message from the server.<br />
     *
     * @return The last message as a string.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-get-last-message.php">mssql_get_last_message</a>
     */
    public function getErrorMessage() {
      return mssql_get_last_message();
    }

    /**
     * Get result data.<br />
     * Returns the contents of one cell from a MS SQL result set.<br />
     *
     * @param $Result The result resource that is being evaluated. This result
     * comes from a call to ExecuteQuery().
     *
     * @param $row The row number.
     *
     * @param $field Can be the field's offset, the field's name or the field's
     * table dot field's name (tablename.fieldname). If the column name has been
     * aliased ('select foo as bar from...'), it uses the alias instead of the
     * column name.
     *
     * @return The contents of the specified cell.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-result.php">mssql_result()</a>
     */
    public function getResult($Result, $row, $field) {
      if (Validator::Validate($Result, Validator::RESOURCE) &&
              Validator::Validate($row, Validator::INTEGER))
        return mssql_result($Result, $row, $field);
    }

    /**
     * Gets the number of rows in result.<br />
     * Returns the number of rows in a result set.<br />
     *
     * @param $Result The result resource that is being evaluated. This result
     * comes from a call to ExecuteQuery().
     *
     * @return The number of rows, as an integer.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-num-rows.php">mssql_num_rows()</a>
     */
    public function getRowsNumber($Result) {
      if (is_resource($Result))
        return mssql_num_rows($Result);
      else
        throw new IllegalArgumentException();
    }

    /**
     * Free result memory.<br />
     * FreeResult() only needs to be called if you are worried about using too
     * much memory while your script is running. All result memory will
     * automatically be freed when the script ends. You may call FreeResult()
     * with the result identifier as an argument and the associated result
     * memory will be freed.<br />
     *
     * @param $Result The result resource that is being evaluated. This result
     * comes from a call to ExecuteQuery().
     *
     * @return TRUE on success or FALSE on failure.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-free-result.php">mssql_free_result()</a>
     */
    public function freeResult($Result) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        return mssql_free_result($Result);
    }

    /**
     * Free statement memory.<br />
     * FreeStatement() only needs to be called if you are worried about using
     * too much memory while your script is running. All statement memory will
     * automatically be freed when the script ends. You may call FreeStatement()
     * with the statement identifier as an argument and the associated statement
     * memory will be freed.<br />
     *
     * @return TRUE on success or FALSE on failure.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-free-statement.php">mssql_free_statement()</a>
     */
    public function freeStatement() {
      return mssql_free_statement($this->resource);
    }

    //function mssql_guid_string()

    /**
     * Returns the last IDENTITY value produced on a connection and by a
     * statement in the same scope, regardless of the table that produced the
     * value.<br />
     *
     * @return mixed Returns an integer that represents the last inserted id or
     * FALSE if the resource is not connected.<br /><br />
     *
     * @see <a href="http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000">SCOPE_IDENTITY()</a>
     */
    public function getScopeIdentity() {
      if ($this->isConnected()) {
        $Result = $this->FetchAssoc($this->ExecuteQuery("SELECT SCOPE_IDENTITY()"));

        return $Result["computed"];
      }
      else
        return FALSE;
    }

    /**
     * Initializes a stored procedure or a remote stored procedure.<br />
     *
     * @param $stored_procedure Stored procedure name, like ownew.sp_name or
     * otherdb.owner.sp_name.
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-init.php">mssql_init()</a>
     */
    public function init($stored_procedure) {
      $this->resource = mssql_init($stored_procedure, $this->handle);
    }

    /**
     * Move the internal result pointer to the next result.<br />
     * When sending more than one SQL statement to the server or executing a
     * stored procedure with multiple results, it will cause the server to
     * return multiple result sets. This function will test for additional
     * results available form the server. If an additional result set exists it
     * will free the existing result set and prepare to fetch the rows from the
     * new result set.<br />
     *
     * @param $Result The result resource that is being evaluated. This result
     * comes from a call to ExecuteQuery().
     *
     * @return TRUE if an additional result set was available or FALSE
     * otherwise.<br /><br />
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-next-result.php">mssql_next_result()</a>
     */
    public function nextResult($Result) {
      if (Validator::Validate($Result, Validator::RESOURCE))
        return mssql_next_result($Result);
    }

    /**
     * Sets the lower error severity.<br />
     *
     * @param $severity The new error severity.
     * @deprecated
     * @see <a href="http://be.php.net/manual/en/function.mssql-min-error-severity.php">mssql_min_error_severity()</a>
     */
    public function setMinimumErrorSeverity($severity) {
      if (Validator::Validate($severity, Validator::INTEGER))
        mssql_min_error_severity($severity);
    }

    /**
     * @deprecated
     *
     * Sets the lower message severity.<br />
     *
     * @param $severity The new error severity.
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-min-message-severity.php">mssql_min_message_severity()</a>
     */
    public function setMinimumMessageSeverity($severity) {
      if (Validator::Validate($severity, Validator::INTEGER))
        mssql_min_message_severity($severity);
    }

    /**
     * Select MS SQL database.<br />
     * Sets the current active database on the server. Every subsequent call to
     * ExecuteQuery() will be made on the active database.<br />
     *
     * @param $database The database name. To escape the name of a database that
     * contains spaces, hyphens ("-"), or any other exceptional characters, the
     * database name must be enclosed in brackets, as is shown in the example,
     * below. This technique must also be applied when selecting a database name
     * that is also a reserved word (such as primary).
     *
     * @see <a href="http://be.php.net/manual/en/function.mssql-select-db.php">mssql_select_db()</a>
     */
    public function selectDataBase($database) {
      if (Validator::Validate($database, Validator::STRING))
        return mssql_select_db($database, $this->handle);
    }

  }

?>
